using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace SqlServerWebServerConsole
{

  internal class SqlHelper
  {

    //public static readonly string connstr = "Data Source=192.168.75.118;Initial Catalog=Mechanic_GL;User ID=sa;Password=hohnroot";
    public static readonly string connstr = ConfigurationManager.AppSettings["connstr"].ToString();


    public static int ExecuteNonQuery(string sql,
        params SqlParameter[] parameters)
    {
      using (SqlConnection conn = new SqlConnection(connstr))
      {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
          cmd.CommandText = sql;
          cmd.Parameters.AddRange(parameters);
          return cmd.ExecuteNonQuery();
        }
      }
    }

    public static object ExecuteScalar(string sql,
        params SqlParameter[] parameters)
    {
      using (SqlConnection conn = new SqlConnection(connstr))
      {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
          cmd.CommandText = sql;
          cmd.Parameters.AddRange(parameters);
          return cmd.ExecuteScalar();
        }
      }
    }

    public static DataTable? ExecuteDataTable(string sql,
        params SqlParameter[] parameters)
    {
      using (SqlConnection conn = new SqlConnection(connstr))
      {
        try
        {
          Console.WriteLine(conn.ConnectionTimeout);
          conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
          cmd.CommandText = sql;
          cmd.Parameters.AddRange(parameters);

          DataSet dataset = new DataSet();
          SqlDataAdapter adapter = new SqlDataAdapter(cmd);
          adapter.Fill(dataset);
          return dataset.Tables[0];
        }
        }catch(Exception ex)
        {
          Console.WriteLine(ex.Message);

          return null;
        }
        
      }
    }

    public static object FromDbValue(object value)
    {
      if (value == DBNull.Value)
      {
        return null;
      }
      else
      {
        return value;
      }
    }

    public static object ToDbValue(object value)
    {
      if (value == null)
      {
        return DBNull.Value;
      }
      else
      {
        return value;
      }
    }
  }
}
